DROP EXTENSION pgrxdemo;
CREATE EXTENSION pgrxdemo;

SELECT * FROM pg_available_extensions
ORDER BY name;

CREATE EXTENSION tablefunc;

SELECT cal_bonus('2023-10-10',100);

CREATE TABLE emps(id int ,name varchar(255),age int);

INSERT INTO emps(id,name,age) VALUES 
(1,'allen',32),
(2,'bill',35),
(3,'tom',30),
(4,'lily',25);

SELECT * FROM emps;

SELECT id,name,age,age_add(age)  FROM emps;

SELECT id,name,age,age+1 as sql_add_age  
FROM emps;


select count(1) from tab_emps;
select *  from tab_emps
limit 10;

EXPLAIN ANALYZE
WITH a AS (
SELECT *,
    CASE
    WHEN date_part('year',age(now(),indate)) >= 10 
        THEN 2 + date_part('year',age(now(),indate))* 0.015

    WHEN date_part('year',age(now(),indate)) <= 1 
        THEN 2

    ELSE 2 + date_part('year',age(now(),indate))* 0.01
    END as xs
FROM tab_emps )
SELECT * ,pay*xs FROM a;

EXPLAIN ANALYZE
select * ,cal_bonus(indate,pay) from tab_emps;


EXPLAIN ANALYZE 
SELECT *,pay +1 AS add_1 FROM tab_emps;

EXPLAIN ANALYZE
SELECT * ,age_add(pay) FROM tab_emps;

drop table tab_emps_t12

EXPLAIN ANALYZE
CREATE TABLE tab_emps_t12
AS 
SELECT *,pay +1 AS add_1 FROM tab_emps;

drop table tab_emps_t13;
EXPLAIN ANALYZE
CREATE TABLE tab_emps_t13
AS 
SELECT *,age_add(pay) AS add_1 FROM tab_emps;

EXPLAIN ANALYZE
UPDATE tab_emps_t12 SET pay = pay +1;

EXPLAIN ANALYZE
UPDATE tab_emps_t13 SET pay = age_add(pay);


drop table tab_emps_t14;
EXPLAIN ANALYZE
CREATE TABLE tab_emps_t14
AS 
WITH a AS (
SELECT *,
    CASE
    WHEN date_part('year',age(now(),indate)) >= 10 
        THEN 2 + date_part('year',age(now(),indate))* 0.015

    WHEN date_part('year',age(now(),indate)) <= 1 
        THEN 2

    ELSE 2 + date_part('year',age(now(),indate))* 0.01
    END as xs
FROM tab_emps )
SELECT * ,pay*xs FROM a;

drop table tab_emps_t15;
EXPLAIN ANALYZE
CREATE TABLE tab_emps_t15
AS 
select * ,cal_bonus(indate,pay) from tab_emps;


EXPLAIN ANALYZE
UPDATE tab_emps_t14 SET pay = b.p from (
WITH a AS (
SELECT id,pay,
    CASE
    WHEN date_part('year',age(now(),indate)) >= 10 
        THEN 2 + date_part('year',age(now(),indate))* 0.015

    WHEN date_part('year',age(now(),indate)) <= 1 
        THEN 2

    ELSE 2 + date_part('year',age(now(),indate))* 0.01
    END as xs
FROM tab_emps_t14 WHERE id < 1000)
SELECT pay*xs as p FROM a WHERE id < 1000) as b
WHERE id < 1000;


EXPLAIN ANALYZE
UPDATE tab_emps_t15 SET pay = cal_bonus(indate,pay)




EXPLAIN ANALYZE
SELECT * FROM tab_emps 
LIMIT 10;

EXPLAIN ANALYZE
WITH graduate AS (
SELECT 
    CASE
    WHEN date_part('year',age('2023-12-30',birthday)) >= 35 
                    AND date_part('year',age('2023-12-30',indate)) >= 10
                    THEN  '35岁以上工作满10年'
    WHEN date_part('year',age('2023-12-30',birthday)) >= 35 
                    AND date_part('year',age('2023-12-30',indate)) >= 5
                    AND date_part('year',age('2023-12-30',indate)) < 10
        THEN  '35岁以上工作满5年不满10年'
    WHEN date_part('year',age('2023-12-30',birthday)) >= 35 
                    AND date_part('year',age('2023-12-30',indate)) >= 1
                    AND date_part('year',age('2023-12-30',indate)) < 5
        THEN  '35岁以上工作满1年不满5年'
    WHEN date_part('year',age('2023-12-30',birthday)) >= 35 
                    AND date_part('year',age('2023-12-30',indate)) < 1
        THEN  '35岁以上工作不满1年'
    
    WHEN date_part('year',age('2023-12-30',birthday)) < 35 
        THEN  '不满35岁'
    END as ga
FROM tab_emps)
SELECT ga,count(ga) FROM graduate
GROUP BY(ga);

EXPLAIN ANALYZE
SELECT cal_graduate(indate,birthday)  as ga,
count(cal_graduate(indate,birthday))
FROM tab_emps 
GROUP BY ga;


SELECT * FROM my_generate_series(0,5,1);

SELECT * FROM my_generate_series2(0,5,1);

EXPLAIN ANALYZE
SELECT * FROM my_generate_series(0,50000,1);

EXPLAIN ANALYZE
SELECT * FROM my_generate_series2(0,50000,1);


SELECT my_uuid7()

DROP TABLE tab_uuid7;

CREATE TABLE tab_uuid7  AS
select my_uuid7() from my_generate_series(1,20000,1);

SELECT count(1) FROM tab_uuid7;
SELECT * FROM tab_uuid7 limit 10;



WITH u AS(
    SELECT row_number() over(ORDER BY my_uuid7) as rows
    ,my_uuid7 
    FROM tab_uuid7)
SELECT * FROM u
WHERE u.rows in 
(SELECT FLOOR(normal_rand(10,10000,1000)))


WITH u AS(
    SELECT row_number() over(ORDER BY my_uuid7) as rows
    ,my_uuid7 
    FROM tab_uuid7)
SELECT * FROM u
WHERE u.rows in 
(SELECT my_generate_possion(18000.0,20))

WHERE my_uuid7 >= '0187faaf-0287-786c-96af-936bb6ca0f8c'
AND my_uuid7 <= '0187faaf-0287-786c-96af-93785cfb005f'

SELECT * FROM tab_myvalues
limit 10

SELECT get_values()

SELECT * FROM get_values()

SELECT * FROM random_values(100)
WHERE value > 0.95

SELECT name,year,month,value as  t1 
    FROM tab_myvalues
    WHERE year = 2015 AND month = 8;

SELECT name,year,month,UNNEST(value) as  t1 
    FROM tab_myvalues
    WHERE year = 2015 AND month = 8

WITH 
    vmax AS(
        WITH v AS (
            SELECT name,year,month, UNNEST(value) as  t1 
            FROM tab_myvalues
            WHERE year = 2015 AND month = 8)
        SELECT name,year,month,max(v.t1) FROM v
        GROUP BY name,year,month),
    value AS
        (SELECT * from tab_myvalues WHERE year = 2015 AND month = 8)
SELECT 
    value.name,value.year,value.month,
    vmax.max,array_position(value.value,vmax.max)FROM vmax,value
WHERE 
 value.name = vmax.name

SELECT * from get_max_value(ARRAY[1,2,3,4,5,6])

SELECT name,year,month, v.day,v.maxvalue,value
    FROM tab_myvalues,get_max_value(value) as v

SELECT * from get_day_value(ARRAY[1,2,3,4,5,6])

WITH tv AS(SELECT t.id,t.name,t.year,t.month,  
v.day,v.value
FROM tab_myvalues t ,get_day_value(value) v)
SELECT name,year,month,
max(value),min(value) 
FROM tv
GROUP BY name,year,month

SELECT * FROM get_random_table(10);

CREATE TABLE random_tab AS 
SELECT * FROM get_random_table(1000);

SELECT * FROM random_tab
limit 10;

SELECT COUNT(1) FROM random_tab;

SELECT * FROM get_csv_string('/home/godxia/data/bjstreet.csv')
limit 10;

SELECT * FROM get_csv_string('/home/godxia/data/bjstreet.csv')
WHERE value like '%门%'

SELECT * FROM 
get_csv_string('/home/godxia/data/bjstreet.csv')  bj,
get_csv_value('/home/godxia/data/job.csv') job
where bj.id = job.id

SELECT * FROM
get_shp_dltb('/home/godxia/data/dltb/dltb.shp')

SELECT "DLMC",count("DLMC"),sum("TBMJ") FROM
get_shp_dltb('/home/godxia/data/dltb/dltb.shp')
GROUP BY "DLMC"